﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace CommandLib
{
    /// <summary>
    /// EPP操作类
    /// </summary>
    public class EPPlus
    {
        /// <summary>
        /// 奖数据转换成EXECL
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <param name="strFilePath">文件咱径</param>
        public static void ToExcel(DataTable dt, string strFilePath,string sheetname= "Sheet1")
        {
            try
            {
                FileInfo file = new FileInfo(strFilePath);
                using (ExcelPackage ep = new ExcelPackage(file))
                {
                    ExcelWorksheet ws = ep.Workbook.Worksheets.Add(sheetname);
                    ws.Cells["A1"].LoadFromDataTable(dt, true);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        string dtcolumntype = dt.Columns[j].DataType.Name.ToLower();
                        if (dtcolumntype == "datetime")
                        {
                            ws.Column(j + 1).Style.Numberformat.Format = "yyyy/mm/dd hh:mm:ss";
                        }
                    }

                    ep.Save();                   
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 按模板导出EXECL
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <param name="strTemplete">文件路径模板</param>
        /// <param name="strExcelFile">文件路径</param>
        /// <param name="intSheet">sheet索引</param>
        public static void ToExcel(DataTable dt, string strTemplete, string strExcelFile, int intSheet)
        {
            FileInfo strTemp = new FileInfo(strTemplete);
            FileInfo strNewTemp = new FileInfo(strExcelFile);
            try
            {
                ExcelPackage package = new ExcelPackage(strTemp);
                //获取总Sheet页
                int vSheetCount = package.Workbook.Worksheets.Count;
                //选定 指定页   
                ExcelWorksheet worksheet = package.Workbook.Worksheets[intSheet];    

                worksheet.Cells["A1"].LoadFromDataTable(dt, true);

                package.SaveAs(strNewTemp);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 将两个数据源输出到同一个EXECL文件中的两个SHEET中
        /// </summary>
        /// <param name="dt1">数据一</param>
        /// <param name="dt2">数据二</param>
        /// <param name="strTemplete">模板文件</param>
        /// <param name="strExcelFile">目标文件</param>
        public static void multipleToExcel(DataTable dt1, DataTable dt2, string strTemplete, string strExcelFile)
        {
            FileInfo strTemp = new FileInfo(strTemplete);
            FileInfo strNewTemp = new FileInfo(strExcelFile);
            try
            {
                ExcelPackage package = new ExcelPackage(strTemp);
                int vSheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页

                ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet2"];//选定 表
                worksheet.Cells["A1"].LoadFromDataTable(dt1, true);

                worksheet = package.Workbook.Worksheets["Sheet3"];//选定 指定页
                worksheet.Cells["A1"].LoadFromDataTable(dt2, true);

                package.SaveAs(strNewTemp);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获取EXECL中的数据
        /// </summary>
        /// <param name="strFilePath">文件名</param>
        /// <param name="tbName">表名</param>
        /// <returns></returns>
        public static DataTable GetExcel(string strFilePath, string sheetName)
        {
            try
            {
                FileInfo file = new FileInfo(strFilePath);
                using (ExcelPackage ep = new ExcelPackage(file))
                {
                    ExcelWorksheet ws = ep.Workbook.Worksheets[sheetName];
                    int maxColumnNum = ws.Dimension.End.Column;//最大列
                    int minColumnNum = ws.Dimension.Start.Column;//最小列

                    int maxRowNum = ws.Dimension.End.Row;//最小行
                    int minRowNum = ws.Dimension.Start.Row;//最大行

                    DataTable vTable = new DataTable();
                    DataColumn vC;
                    for (int j = 1; j <= maxColumnNum; j++)
                    {
                        vC = new DataColumn("A_" + j, typeof(string));
                        vTable.Columns.Add(vC);
                    }

                    for (int n = 2; n <= maxRowNum; n++)
                    {
                        DataRow vRow = vTable.NewRow();
                        for (int m = 1; m <= maxColumnNum; m++)
                        {
                            vRow[m - 1] = ws.Cells[n, m].Value;
                        }
                        vTable.Rows.Add(vRow);
                    }
                    return vTable;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 指定获取sheet中数据据
        /// </summary>
        /// <param name="strFilePath">文件名</param>
        /// <param name="tbIndex">sheet索引</param>
        /// <returns></returns>
        public static DataTable GetExcel(string strFilePath, int tbIndex=1)
        {
            
            try
            {
                FileInfo file = new FileInfo(strFilePath);
                using (ExcelPackage ep = new ExcelPackage(file))
                {
                    ExcelWorksheet ws = ep.Workbook.Worksheets[tbIndex];
                    int maxColumnNum = ws.Dimension.End.Column;//最大列
                    int minColumnNum = ws.Dimension.Start.Column;//最小列

                    int maxRowNum = ws.Dimension.End.Row;//最小行
                    int minRowNum = ws.Dimension.Start.Row;//最大行

                    DataTable vTable = new DataTable();
                    DataColumn vC;
                    for (int j = 1; j <= maxColumnNum; j++)
                    {
                        vC = new DataColumn("A_" + j, typeof(string));
                        vTable.Columns.Add(vC);
                    }

                    for (int n = 2; n <= maxRowNum; n++)
                    {
                        DataRow vRow = vTable.NewRow();
                        for (int m = 1; m <= maxColumnNum; m++)
                        {
                            vRow[m - 1] = ws.Cells[n, m].Value;
                        }
                        vTable.Rows.Add(vRow);
                    }
                    return vTable;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


    }
}
